import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime
import openpyxl


def formatLink(shopName):
    # return shopName.split('-')[1]
    # return shopName.replace('拼多多-', '')
    return shopName


engine = create_engine('mysql+pymysql://jsbi:jsbi-1701@47.114.55.19:9011/biv1?charset=utf8')
con = engine.connect()

# 所谓的信誉订单，也就是种草，刷单订单
# 这个数据来自于ERP系统
# 信誉订单导出_20200812134840.xlsx
# 信誉订单导出_20200817092621.xlsx
df = pd.read_excel('D:/简尚家居/钉钉下载目录/信誉订单导出_20200817092621.xlsx', sheet_name=0)
# dfMid = df[df['汇总'].isnull()]
# dfClean = dfMid.drop(columns=['汇总'])
# df['店铺_云杉ERP'] = df['店铺'].apply(formatLink)
# 解决可能出现的超时问题bugfix 2020.08.18
con.connection.connection.ping(reconnect=True)
df.to_sql(name='云杉ERP_信誉订单总表', con=con, if_exists='replace', index=False)
debug = ''
